﻿-- =============================================
-- Author:		zengfei
-- Create date: 03m/15/2012
-- Description:	更新库存调整明细表
-- =============================================
CREATE PROCEDURE [dbo].[Usp_ModifyWarehouseAdjustDetail]
	@p_Action	char,					-- A:新增, C:修改, D:删除
	@p_BillType	bit,					-- 报损单: False; 报溢单: True
	@p_Warehouse	int,				-- 指定具体仓库，更新Balance时需要
	@p_BillNo	varchar(30),
	@p_LineNo	varchar(3),
	@p_ProductNo	varchar(10), 
	@p_LotNo	varchar(20),
	@p_ProductDate	datetime,
	@p_Qty	numeric(10, 2), 
	@p_AidQty	numeric(10, 2),
	@p_Location	varchar(20),
	@p_Remark	varchar(60),
	@p_RecordID	int out,
	@p_UserID	varchar(60),
	@p_ErrMsg	varchar(100) out,
	@p_RFlag	smallint out
AS
declare @TmpQty	numeric(10, 2),
		@TmpAidQty	numeric(10, 2),
		@CurQty	numeric(10, 2),
		@CurAidQty	numeric(10, 2),
		@CBalQty	numeric(10, 2),
		@CBalAidQty	numeric(10, 2)

select @CurQty = Qty, @CurAidQty = AidQty from Balance where ProductNo = @p_ProductNo and Warehouse = @p_Warehouse
	
begin transaction
	
if	@p_Action = 'A'
begin
	select @TmpQty = @p_Qty, @TmpAidQty = @p_AidQty
	
	if	@p_BillType = 0			-- 报损单
	begin
		if	@CurQty < @TmpQty or @CurAidQty < @TmpAidQty
		begin
			raiserror('当前商品[%s]库存不足', 16, 1, @p_ProductNo)
			goto ErrHandle
		end
	end
	
	insert into WarehouseAdjustDetail (BillNo, [LineNo], ProductNo, LotNo, ProductDate, Qty, AidQty, Location, Remark)
		values (@p_BillNo, @p_LineNo, @P_ProductNo, @p_LotNo, @p_ProductDate, @p_Qty, @p_AidQty, @p_Location, @p_Remark)
		
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	select @p_RecordID = @@IDENTITY
	
	select @CBalQty = (case when @p_BillType = 1 then @TmpQty else -@TmpQty end), 
			@CBalAidQty = (case when @p_BillType = 1 then @TmpAidQty else -@TmpAidQty end)
end
else if	@p_Action = 'C'
begin
	
	select @TmpQty = @p_Qty - Qty, @TmpAidQty = @p_AidQty - AidQty from WarehouseAdjustDetail where RecordId = @p_RecordId
	
	update WarehouseAdjustDetail set LotNo = @p_LotNo,
								ProductDate = @p_ProductDate,
								Qty = @p_Qty, 
								AidQty = @p_AidQty, 
								Location = @p_Location,
								Remark = @p_Remark 
							where RecordID = @p_RecordID 
	if	@@ROWCOUNT = 0
	begin
		raiserror('当前修改的库存调整记录不存在', 16, 1)
		goto ErrHandle
	end
	
	if	@p_BillType = 1
	begin
		if	(@TmpQty < 0 and @CurQty < -@TmpQty) or (@TmpAidQty < 0 and @CurAidQty < -@TmpAidQty)
		begin
			raiserror('当前商品[%s]库存不足', 16, 1, @p_ProductNo)
			goto ErrHandle
		end
	end
	else
	begin
		if	(@TmpQty > 0 and @CurQty < @TmpQty) or (@TmpAidQty > 0 and @CurAidQty < @TmpAidQty)
		begin
			raiserror('当前商品[%s]库存不足', 16, 1, @p_ProductNo)
			goto ErrHandle
		end
	end
	
	select @CBalQty = (case when @p_BillType = 1 then @TmpQty else -@TmpQty end), 
			@CBalAidQty = (case when @p_BillType = 1 then @TmpAidQty else -@TmpAidQty end)
end
else if	@p_Action = 'D'
begin
	select @TmpQty = @p_Qty, @TmpAidQty = @p_AidQty
	
	if	@p_BillType = 1				-- 报溢单
	begin
		if	@CurQty < @TmpQty or @CurAidQty < @TmpAidQty
		begin
			raiserror('当前商品[%s]库存不足', 16, 1, @p_ProductNo)
			goto ErrHandle
		end
	end
	
	delete from WarehouseAdjustDetail where RecordID = @p_RecordID 
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	select @CBalQty = (case when @p_BillType = 1 then -@TmpQty else @TmpQty end),
			@CBalQty = (case when @p_BillType = 1 then -@TmpAidQty else @TmpAidQty end)
end

-- 写入Log档
insert into WarehouseAdjustDetail_log (BillNo, [LineNo], ProductNo, LotNo, ProductDate, Qty, AidQty, Location, Remark, RecordID, UserID, ModifyTime, [Action])
		values (@p_BillNo, @p_LineNo, @p_ProductNo, @p_LotNo, @p_ProductDate, @p_Qty, @p_AidQty, @p_Location, @p_Remark, @p_RecordID, @p_UserID, GETDATE(), @p_Action)
		
if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
exec Usp_UpdateBalance @p_ProductNo, @p_Warehouse, @CBalQty, @CBalAidQty

if	@@ERROR <> 0 goto ErrHandle

commit transaction

set @p_RFlag = 1


ErrHandle:

if	@@TRANCOUNT > 0	rollback transaction

set @p_RFlag = -1